

** make a table of bounds

/*


load and collapse to weekly sums
save as tempory files
stitch together 


*/

scalar n_pop = 6.6e6 
********************************************************************************
********************************************************************************
** load and save all tests
********************************************************************************
********************************************************************************

** all tests

use health/test_daily_panel, clear
merge m:1 sid using health/demographics, keep(1 3) assert(2 3) ///
	nogen keepusing(sid age_wide)

	** aggregate 
	egen week = cut(date), at(`=mdy(3,13,2020)'(7)`=mdy(12,22,2020)')			
		
	
	** tested/positive at least once in this week?
	collapse (max) positive tested, by(week sid)	
	collapse (sum) tested positive, by(week)
	
	gen test_rate = tested / n_pop
	
	list if week == mdy(3,27,2020)

	format week %td
	list, noobs clean
	
	gen group ="All"
	save health/all_weekly_wtd, replace 

********************************************************************************
********************************************************************************
** load and save the hospital tests, by group
********************************************************************************
********************************************************************************
foreach group in  no_icli any_major any_icli pool_icli {
	
	
	* load the data
	use health/inp_clean if ~no_dx, clear
	if "`group'" == "no_icli" gen no_icli = any_icli == 0
	if "`group'" == "pool_icli" gen pool_icli = 1
	keep if `group' == 1

	egen week = cut(admit_date), at(`=mdy(3,13,2020)'(7)`=mdy(12,22,2020)')		
	
	drop if missing(week)
	collapse (sum) test_tight (count) n=test_tight, by(week)
	gen test_rate = test_tight / n
	
	collapse (mean) test_rate (rawsum) n [fw=n], by(week)
	
	gen group = "`group'"
	save health/`group'_weekly_wtd, replace

	
}

********************************************************************************
********************************************************************************
** append together etc
********************************************************************************
********************************************************************************

clear
foreach group in all any_icli no_icli any_major pool_icli {
	append using health/`group'_weekly_wtd
}



gen groupn = 1 if group == "All"
replace groupn = 2 if group == "no_icli"
replace groupn = 3 if group == "any_major"
replace groupn = 4 if group == "any_icli"
replace groupn = 5 if group == "pool_icli"

keep test_rate n group week groupn
reshape wide test_rate n group, i(week) j(groupn) 


gen month = month(week)
gen day = day(week)
gen year = year(week)
tostring month day year, replace
replace day = "0" + day if length(day)==1
gen date_str = month + "/" + day + "/" + year  + " & " 

forvalues g = 1/5 {
	gen s = string(test_rate`g', "%4.3f") + " & " 
	drop test_rate`g'
	rename s test_rate`g'
	gen s = string(n`g', "%12.0fc") + " & "
	drop n`g'
	rename s n`g'
}
replace test_rate4 = subinstr(test_rate4, "&", "\\",.)

drop if missing(week)
gen weeks = string(week, "%td") 
replace weeks  = subinstr(weeks, "2020", "", .) + " & "

outsheet weeks test_rate1 n2 test_rate2 n3 test_rate3 n4 test_rate4 ///
	using tables/test_rate_by_group.tex, replace noquote nonames delimit(" ")
	
	
	
